SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TCMOVI_BANC_Q22]
/*-----------------------------------------------------------*/
/*-- Empresa		:  OFISIS S.A.			   --*/
/*-- Cliente    	:  OFISIS				   --*/
/*-- Sistema      	:  CAJA Y BANCOS		   --*/
/*-- Modulo      	:  EMPRESA			   --*/
/*-- Programa   	:  REPORTE DE POSICION EN BANCOS   --*/
/*-- Script		:  SP_TCMOVI_BANC_Q01.SQL	   --*/
/*-- Nombre SP		:  SP_TCMOVI_BANC_Q01 		   --*/
/*-- Desarrollado por 	:  ANTONIO ROJAS MENDOZA	   --*/
/*-- Fecha		:  05/08/98 			   --*/
/*-- Base de Datos	:  MICROSOFT SQL SERVER 7.0	   --*/
/*-----------------------------------------------------------*/

/*-----------------------------------------------------------*/
/*-- MODIFICADO POR 	:  EDUARDO VIVAR		   --*/
/*-- Fecha		:  18/04/2001 			   --*/
/*-- DETALLE	  	:  A SQL			   --*/
/*--							   --*/
/*-----------------------------------------------------------*/

/*--------------------------------------------------------------*/
/*--  DROP PROC SP_TCMOVI_BANC_Q01				*/
/*--  GRANT ALL ON SP_TCMOVI_BANC_Q01 TO PUBLIC 			*/
/*--  SP_TCMOVI_BANC_Q01 'OFISIS', '01', '01', 'SOL', 'SOL', '05/29/2000'  */
/*--------------------------------------------------------------*/
@ISCO_USUA TD_VC_008,
@ISCO_EMPR TD_VC_002,
@ISCO_MONE TD_VC_003,
@IDFE_REPO TD_DT_001,
@ISST_FECH_INGR TD_VC_001,
@ISST_FECH_EGRE TD_VC_001

AS


DECLARE
@OSTI_DOCU_CHEQ TD_VC_003


BEGIN

-- INGRESOS
/* Devuelve el Tipo Documento CHEQUE */    
  Exec SP_TTDOCU_CNTB_Q01 29, @OSTI_DOCU_CHEQ Output 


INSERT INTO #TWPOSI_BANC_Q01_X1( CO_EMPR, CO_BANC, NU_CNTA_BANC, 
		IM_INGR, IM_EGRE, IM_CHEQ_CART )
Select T1.CO_EMPR, T1.CO_BANC, T1.NU_CNTA_BANC,  
SUM( ISNULL(T1.IM_MOVI,0) ), 0, 0
From   TCMOVI_BANC T1, TMCNTA_BANC T2
Where  (@ISCO_EMPR IS NULL OR (T1.CO_EMPR = @ISCO_EMPR AND @ISCO_EMPR IS NOT NULL))
AND (@ISCO_MONE IS NULL OR (T2.CO_MONE = @ISCO_MONE AND @ISCO_MONE IS NOT NULL))
AND T1.TI_MOVI_BANC = 'I'
AND T1.TI_SITU = 'ACT'
AND T1.CO_EMPR = T2.CO_EMPR
AND T1.CO_BANC = T2.CO_BANC
AND T1.NU_CNTA_BANC = T2.NU_CNTA_BANC
AND (  ( T1.FE_OPER = @IDFE_REPO AND ( @ISST_FECH_INGR = 'O' OR NU_CHEQ IS NULL ) )
    OR ( T1.FE_DISP = @IDFE_REPO AND @ISST_FECH_INGR = 'D' AND NU_CHEQ IS NOT NULL ) )
AND Exists ( Select * From  TTUSER_CNBA t4
	Where        T1.CO_EMPR = t4.CO_EMPR
	And            T1.CO_BANC  = t4.CO_BANC
	And            T1.NU_CNTA_BANC = t4.NU_CNTA_BANC
	And            t4.CO_USUA = @ISCO_USUA
	And            t4.ST_REPO = 'S') 
GROUP BY T1.CO_EMPR, T1.CO_BANC, T1.NU_CNTA_BANC;  

-- EGRESOS

INSERT INTO #TWPOSI_BANC_Q01_X1( CO_EMPR, CO_BANC, NU_CNTA_BANC, 
		IM_INGR, IM_EGRE, IM_CHEQ_CART )
Select T1.CO_EMPR, T1.CO_BANC, T1.NU_CNTA_BANC,  
0, SUM( ISNULL(T1.IM_MOVI,0) ), 0
From   TCMOVI_BANC T1, TMCNTA_BANC T2
Where  (@ISCO_EMPR IS NULL OR (T1.CO_EMPR = @ISCO_EMPR AND @ISCO_EMPR IS NOT NULL))
AND (@ISCO_MONE IS NULL OR (T2.CO_MONE = @ISCO_MONE AND @ISCO_MONE IS NOT NULL))
AND T1.TI_MOVI_BANC = 'E'
AND T1.TI_SITU = 'ACT'
AND T1.CO_EMPR = T2.CO_EMPR
AND T1.CO_BANC = T2.CO_BANC
AND T1.NU_CNTA_BANC = T2.NU_CNTA_BANC
--AND NOT (T1.NU_CHEQ IS NOT NULL  AND ( T1.FE_CHEQ IS NULL OR ( T1.FE_CHEQ IS NOT NULL  AND T1.FE_CHEQ > @IDFE_REPO )))
AND T1.FE_OPER = @IDFE_REPO

/*AND (  ( T1.FE_OPER = @IDFE_REPO AND ( @ISST_FECH_EGRE = 'O' OR NU_CHEQ IS NULL ) )
    OR ( T1.FE_PROG_CHEQ = @IDFE_REPO AND @ISST_FECH_EGRE = 'P' AND NU_CHEQ IS NOT NULL ) 
    OR ( T1.FE_ENTR_CHEQ = @IDFE_REPO AND @ISST_FECH_EGRE = 'E' AND NU_CHEQ IS NOT NULL ) 
    OR ( T1.FE_CHEQ = @IDFE_REPO AND @ISST_FECH_INGR = 'C' AND NU_CHEQ IS NOT NULL ) 	)*/
AND Exists ( Select * From  TTUSER_CNBA t4
	Where        T1.CO_EMPR = t4.CO_EMPR
	And            T1.CO_BANC  = t4.CO_BANC
	And            T1.NU_CNTA_BANC = t4.NU_CNTA_BANC
	And            t4.CO_USUA = @ISCO_USUA
	And            t4.ST_REPO = 'S') 
GROUP BY T1.CO_EMPR, T1.CO_BANC, T1.NU_CNTA_BANC


-- CHEQUES EN CARTERA

INSERT INTO #TWPOSI_BANC_Q01_X1( CO_EMPR, CO_BANC, NU_CNTA_BANC, 
		IM_INGR, IM_EGRE, IM_CHEQ_CART )
Select T1.CO_EMPR, T1.CO_BANC, T1.NU_CNTA_BANC, 

0, 0, SUM( ISNULL(T1.IM_MOVI,0) )
From   TCMOVI_BANC T1, TMCNTA_BANC T2
Where  (@ISCO_EMPR IS NULL OR (T1.CO_EMPR = @ISCO_EMPR AND @ISCO_EMPR IS NOT NULL))
AND (@ISCO_MONE IS NULL OR (T2.CO_MONE = @ISCO_MONE AND @ISCO_MONE IS NOT NULL))
AND T1.TI_MOVI_BANC = 'E'
AND T1.TI_SITU = 'ACT'
AND T1.CO_TIPO_DOCU = @OSTI_DOCU_CHEQ
AND T1.NU_CHEQ IS NOT NULL
AND (T1.FE_CHEQ IS NULL OR ( T1.FE_CHEQ IS NOT NULL  AND T1.FE_CHEQ > @IDFE_REPO )) 
AND T1.CO_EMPR = T2.CO_EMPR
AND T1.CO_BANC = T2.CO_BANC
AND T1.NU_CNTA_BANC = T2.NU_CNTA_BANC
AND Exists ( Select * From  TTUSER_CNBA t4
	Where        T1.CO_EMPR = t4.CO_EMPR
	And            T1.CO_BANC  = t4.CO_BANC
	And            T1.NU_CNTA_BANC = t4.NU_CNTA_BANC
	And            t4.CO_USUA = @ISCO_USUA
	And            t4.ST_REPO = 'S') 
GROUP BY T1.CO_EMPR, T1.CO_BANC, T1.NU_CNTA_BANC;

INSERT INTO #TWPOSI_BANC_Q01_X2( CO_EMPR, CO_BANC, NU_CNTA_BANC, 
		IM_INGR, IM_EGRE, IM_CHEQ_CART )
Select CO_EMPR, CO_BANC, NU_CNTA_BANC, 
	SUM(IM_INGR), SUM(IM_EGRE), SUM(IM_CHEQ_CART)
FROM #TWPOSI_BANC_Q01_X1
GROUP BY CO_EMPR, CO_BANC, NU_CNTA_BANC;

-- INSERTO CUENTAS QUE NO HAN TENIDO MOVIMIENTOS, ES DECIR AUN NO ESTAN GRABADAS EN TABLA #TWSABA REPO

INSERT INTO #TWPOSI_BANC_Q01_X2( CO_EMPR, CO_BANC, NU_CNTA_BANC, 
		IM_INGR, IM_EGRE, IM_CHEQ_CART )
SELECT T2.CO_EMPR, T2.CO_BANC, T2.NU_CNTA_BANC, 0, 0, 0
FROM TMCNTA_BANC T2
WHERE NOT EXISTS
(SELECT T1.CO_EMPR FROM #TWPOSI_BANC_Q01_X2 T1
WHERE RTRIM(T1.CO_EMPR) = RTRIM(T2.CO_EMPR)
AND RTRIM(T1.CO_BANC) = RTRIM(T2.CO_BANC) 
AND RTRIM(T1.NU_CNTA_BANC) = RTRIM(T2.NU_CNTA_BANC))


END


GO
